package com.insigma.yanwenlong.bookkeeping.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.insigma.yanwenlong.bookkeeping.db.jdbc.DBConnection;
import com.insigma.yanwenlong.bookkeeping.pojo.Member;

public class MemberDaoImpl implements MemberDao {

	@Override
	public List<Member> findallMembers() throws SQLException {
		Connection connection =DBConnection.getConnection();
		String sql = "select * from member";
		Statement statement =connection.createStatement();
		ResultSet resultSet = statement.executeQuery(sql);
		List<Member> members = new ArrayList<Member>();
		
		while(resultSet.next()) {
			Member member = new Member();
			member.setId(resultSet.getInt("id"));
			member.setName(resultSet.getString("name"));
			
			members.add(member);
		}
		return members;
	}

	@Override
	public Member findMemberById(int id) throws SQLException {
		Connection connection = DBConnection.getConnection();
		String sql ="select * from member where id = ?";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setInt(1, id);
		ResultSet resultSet = preparedStatement.executeQuery();
		Member member = new Member();
		if(resultSet.next()) {
			member.setId(resultSet.getInt("id"));
			member.setName(resultSet.getString("name"));
		}
		
		DBConnection.closeResultSet(resultSet);
		DBConnection.closeStatement(preparedStatement);
		DBConnection.closeConnection(connection);
		
		return member;
	}

	@Override
	public int addMember(Member member) throws SQLException {
		Connection connection = DBConnection.getConnection();
		String sql ="insert into member (name) values(?)";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setString(1, member.getName());
		int rowAffected = preparedStatement.executeUpdate();
		
		DBConnection.closeStatement(preparedStatement);
		DBConnection.closeConnection(connection);
		
		return rowAffected;
	}

	@Override
	public boolean deleteMemberById(int id) throws SQLException {
		Connection connection = DBConnection.getConnection();
		String sql ="delete from member where id = ?";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setInt(1, id);
		boolean isSuccess;
		isSuccess = preparedStatement.execute();
		return isSuccess;
	}

}
